with sign_waybill as (  --最早签收时间等于t-1的签收单号
    select
        waybill_no
         ,scan_time
         ,scan_site
         ,scan_site_code
    from
        (
            select
                waybill_no
                 ,scan_time
                 ,scan_site
                 ,scan_site_code
                 ,row_number() over(partition by waybill_no order by scan_time) as rk
            from
                (
                    SELECT
                        waybill_no
                         ,signed_time as scan_time
                         ,signed_site as scan_site
                         ,signed_site_code as scan_site_code
                    FROM jms_dwd.dwd_tab_barscan_sign_base_dt
                    where dt between date_add('{{ execution_date | cst_ds }}',-29) and '{{ execution_date | cst_ds }}'
                    union all
                    select
                        waybill_no
                         ,scan_time
                         ,scan_site
                         ,scan_site_code
                    from jms_dwd.dwd_tab_barscan_collect_base_dt
                    where dt between date_add('{{ execution_date | cst_ds }}',-29) and '{{ execution_date | cst_ds }}'
                      and scan_type='出库扫描'
                )t1
        )t2 where rk=1 and date(scan_time)='{{ execution_date | cst_ds }}' and scan_site_code is not null and substr(waybill_no,1,2)='JT'
    ),
--逻辑处理后的轨迹下一站
    tab_trace_next as (
select
    waybill_no  --单号
        ,scan_site_code --扫描网点
        ,scan_next_code --扫描下一站
        ,scan_time   --扫描下一站 用于去重，一个中心多次发件取最近的一条
        ,send_station_network_type
        ,send_station_number
    --下一站取集散或者网点，集散和网点在前面出现过不判断，退转件不判断，错分不判断
        ,min(concat(if(send_station_network_type not in (3,4)  ,null,lpad(send_station_number,2,'0')),'_',scan_site_code,'_',is_happen,'_',is_can_cz0,'_',scan_next_code)) over(partition by waybill_no order by send_station_number ROWS BETWEEN 1 FOLLOWING AND  UNBOUNDED FOLLOWING ) as trace_next_code  --逻辑处理后的轨迹实际下一站
from
    (
    select
    waybill_no
        ,scan_site_code
        ,scan_time
        ,send_station_number
        ,if(array_contains(preceding_list,scan_site_code),1,0) as is_happen --是否在之前出现
        ,nvl(scan_next_code,'') as scan_next_code
        ,send_station_network_type --当前网点类型
        ,if(send_station_arrival_early_time is not null and scan_next_code is not null and scan_time is not null,1,0) as is_can_cz0--到件时间
    from (
    select waybill_no
        , send_station_network_code                                          as     scan_site_code
        , nvl(send_station_loading_later_time, send_station_send_later_time) as     scan_time
        , send_station_number
        , send_station_send_next_network_code  as scan_next_code   --扫描下一站
        , send_station_network_type
        , send_station_arrival_early_time  --发件扫描网点到件时间
        , collect_list(send_station_network_code) over(partition by waybill_no order by send_station_number rows between unbounded preceding and 1 preceding ) as preceding_list  --之前经过站点list
    from jms_dwd.dwd_wide_sign_trace_waybill_step_dt
    where dt = '{{ execution_date | cst_ds }}' --最早签收时间
    --and waybill_no in ('JT2054115115939','JT5225530612702')
    )m)r
    ),
--中心发件下一站为网点或者集散点 ，下一站为集散点，集散点发件下一站为网点
    center_send as
    (
select
    waybill_no
        ,scan_time
        ,scan_site
        ,scan_site_code
        ,scan_user
        ,scan_user_code
        ,source
        ,pistol_id
        ,next_station_code
        ,next_station
        ,next_network_type
        ,package_code
        ,trace_next_code
        ,is_happen
        ,is_next_network
        ,is_can_cz0
        ,trace_next_next_code
from
    (
    select
    center_se.waybill_no
        ,center_se.scan_time
        ,center_se.scan_site
        ,center_se.scan_site_code
        ,center_se.scan_user
        ,center_se.scan_user_code
        ,center_se.source
        ,center_se.pistol_id
        ,center_se.next_station_code
        ,center_se.next_station
        ,center_se.next_network_type
        ,center_se.package_code
        ,row_number() over(partition by center_se.waybill_no,center_se.scan_site_code order by center_se.scan_time,center_se.input_time desc,distri_send.scan_time,un.scan_time) as rk
        , split(un.trace_next_code,'_')[1] as trace_next_code --轨迹实际下一站集散或者网点
        , split(un.trace_next_code,'_')[2] as is_happen  --之前是否经过该节点
        , split(un.trace_next_code,'_')[3] as is_can_cz0 --是否判断错装
        , split(un.trace_next_code,'_')[4] as trace_next_next_code --是否判断错装
        ,if(distri_send.waybill_no is not null,1,0) as is_next_network  --中心-集散-？是网点为1
    from
    ( --中心发件下一站为网点或者集散点
    select
    waybill_no
        ,scan_time
        ,scan_site
        ,scan_site_code
        ,scan_user
        ,scan_user_code
        ,source
        ,pistol_id
        ,next_station_code
        ,next_network_type
        ,package_code
        ,next_station
        ,input_time
    from jms_dwd.dwd_tab_barscan_centersend_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-29) and '{{ execution_date | cst_ds }}'
    and next_network_type in ('3','4')
    )center_se
    left join
    (--集散点发件下一站为网点
    select
    waybill_no
        ,scan_time
        ,input_site as scan_site
        ,scan_site_code
        ,next_station_code
        ,next_network_type
    from jms_dwd.dwd_tab_barscan_send_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-29) and '{{ execution_date | cst_ds }}'
    and next_network_type  ='4'
    and network_type='3'
    )distri_send on center_se.waybill_no = distri_send.waybill_no and center_se.next_station_code = distri_send.scan_site_code and center_se.scan_time <= distri_send.scan_time
    --取轨迹下一站
    left join tab_trace_next un on un.waybill_no=center_se.waybill_no and un.scan_site_code=center_se.scan_site_code and date_format(un.scan_time,'yyyy-MM-dd HH:mm:ss')>=date_format(center_se.scan_time,'yyyy-MM-dd HH:mm:ss')
    )center_send
where rk=1 and(next_network_type='4' or (next_network_type='3' and is_next_network=1))
    ),
    tab_waybill as (
--运单信息
SELECT
    way.waybill_no
        ,way.first_code
        ,way.second_code
        ,way.third_code
        ,if(way.ordersource_name in ('桃花岛跨境','桃花岛跨境（逆）','桃花岛','紫金山','逍遥峰','极地湾','麦田圈','苏宁','有赞','七星潭','唯品会面单'),way.ordersource_name,'其他')  as ordersource_name
        ,way.receiver_province_id
        ,way.receiver_city_id
        ,way.receiver_area_id
from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt way
where way.dt between date_add('{{ execution_date | cst_ds }}',-45)  and '{{ execution_date | cst_ds }}'
    ),
    tab_taking as --揽收信息
    (
select
    waybill_no
        ,scan_site_code
        ,scan_site
FROM
    (
    select
    waybill_no
        ,scan_site_code
        ,input_site as scan_site
        ,row_number() over(partition by waybill_no order by scan_time) as rk
    from jms_dwd.dwd_tab_barscan_taking_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-29)  and '{{ execution_date | cst_ds }}'
    )t
where t.rk=1
    ),
    tab_delete as ( --发件删除信息
select
    billcode
        ,scan_type
        ,scan_time
        ,scan_site_code
from jms_dwd.dwd_tab_barscan_scan_delete_base_dt
where dt between date_add('{{ execution_date | cst_ds }}',-30) and '{{ execution_date | cst_ds }}'
  and scan_type='发件扫描'
group by
    billcode
        ,scan_type
        ,scan_time
        ,scan_site_code
    ),
    tab_bagging as (  --建包扫描
select waybill_no ,scan_site_code ,package_code,max(scan_site) as scan_site  ,source
from jms_dwd.dwd_tab_barscan_bagging_base_dt
where dt between date_add('{{ execution_date | cst_ds }}',-40) and '{{ execution_date | cst_ds }}'
  and substr(waybill_no,1,1)!='B'
group by waybill_no ,scan_site_code,package_code,source
    ),
    main_detail as (  --取中心发件当时的三段码 主要考虑退转件前后三段码：中心发件后有多次退转取时间最近的，退转在发件后取退转表原三段码，退转在发件前取退转表新三段码
select
    *
from(
    select
    t.*
        ,reback2.terminal_dispatch_code as old_reback_terminal_code
        ,reback2.print_time as reback_apply_time  --退转扫描时间
        ,reback2.receiver_province_id  as old_receiver_province_id
        ,reback2.receiver_city_id      as old_receiver_city_id
        ,reback2.receiver_area_id      as old_receiver_area_id
        ,row_number() over(partition by t.waybill_no,t.center_code,t.next_station_code,t.sign_time
    order by unix_timestamp(reback2.print_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(t.send_time,'yyyy-MM-dd HH:mm:ss'))  as rk2
        ,if(reback2.waybill_no is not null,1,0 ) as is_reback_after --是否退转
        ,reback2.apply_type_code as apply_reback_type --退转类型
    from
    (
    select
    sign_waybill.waybill_no
        ,sign_waybill.scan_time         as sign_time
        ,sign_waybill.scan_site         as sign_site
        ,sign_waybill.scan_site_code    as sign_site_code
        ,center_send.scan_time          as send_time
        ,center_send.scan_site          as center_name
        ,center_send.scan_site_code     as center_code
        ,center_send.scan_user          as scan_user
        ,center_send.scan_user_code     as scan_user_code
        ,center_send.source             as source
        ,center_send.pistol_id          as pistol_id
        ,center_send.next_station_code  as next_station_code
        ,center_send.next_network_type  as next_network_type
        ,center_send.package_code
        ,center_send.next_station
    --,'' as reback_type --退转对应类型（4 转寄5退件）
        ,reback1.new_terminal_dispatch_code as new_reback_terminal_code
    --,reback1.apply_time as reback_apply_time
        ,reback1.new_receiver_province_id
        ,reback1.new_receiver_city_id
        ,reback1.new_receiver_area_id
        ,row_number() over(partition by sign_waybill.waybill_no,center_send.scan_site_code,center_send.next_station_code,sign_waybill.scan_time
    order by unix_timestamp(center_send.scan_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(reback1.print_time,'yyyy-MM-dd HH:mm:ss'))  as rk
        ,if(reback1.waybill_no is not null,1,0 ) as is_reback_before --是否退转
        ,center_send.trace_next_code
        ,center_send.is_happen
        ,center_send.is_can_cz0
        ,center_send.trace_next_next_code
    from sign_waybill
    inner join center_send on sign_waybill.waybill_no = center_send.waybill_no
    ----取中心发件前最近的退转登记的新三段码（原因：可能发生多次退转，运单取到的是最后一次退转三段码）
    left join jms_dwd.dwd_tab_reback_transfer_express_base reback1
    on date(reback1.print_time) between date_add('{{ execution_date | cst_ds }}',-29) and '{{ execution_date | cst_ds }}'
    and reback1.waybill_no=center_send.waybill_no and reback1.print_time<center_send.scan_time
    )t
    ----取中心发件后最近的退转登记的老三段码（原因：可能发生多次退转，运单取到的是最后一次退转三段码）
    --是否退转：是取中心发件之后的退转登记判断错分类型
    left join jms_dwd.dwd_tab_reback_transfer_express_base reback2
    on date(reback2.print_time) between date_add('{{ execution_date | cst_ds }}',-29) and '{{ execution_date | cst_ds }}'
    and reback2.waybill_no=t.waybill_no and reback2.print_time>t.send_time
    where t.rk=1
    )tt where tt.rk2=1

    )
--关联取其他信息形成中间表
insert overwrite table jms_tmp.tmp_end_center_error_scan_detail_mid_dt
select
    main.waybill_no     --运单号
     ,main.sign_time      --签收时间
     ,main.sign_site      --签收网点名称
     ,main.sign_site_code --签收网点code
     ,main.send_time      --中心发件时间
     ,main.center_name    --中心name
     ,main.center_code    --中心code
     ,main.scan_user      --扫描人
     ,main.scan_user_code            --扫描员工号
     ,main.source as send_source                    --中心发件扫描数据来源
     ,main.pistol_id                 --扫描设备编码
     ,main.next_station_code         --发件下一站编码
     ,main.next_network_type         --发件下一站类型
     ,main.next_station              --发件下一站名称
     ,main.package_code  --包号
     ,case when main.is_reback_before=1 then split(main.new_reback_terminal_code,' ')[0]
           when main.is_reback_after=1  then split(main.old_reback_terminal_code,',')[0]
           else tab_waybill.first_code
    end as first_code          --一段码
     ,case when main.is_reback_before=1 then split(main.new_reback_terminal_code,' ')[1]
           when main.is_reback_after=1  then split(main.old_reback_terminal_code,',')[1]
           else tab_waybill.second_code
    end as second_code        --二段码
     ,case when main.is_reback_before=1 then split(main.new_reback_terminal_code,' ')[2]
           when main.is_reback_after=1  then split(main.old_reback_terminal_code,',')[2]
           else tab_waybill.third_code
    end as third_code         --三段码
     ,tab_waybill.ordersource_name    --订单来源
-- ,null as dispatch_network_code                         --运单二段码对应网点code
-- ,null as dispatch_network                              --运单二段码对应网点
     ,tab_taking.scan_site_code as taking_site_code       --揽收网点编码
     ,tab_taking.scan_site as taking_site                 --揽收网点name
     ,coalesce (electr.package_code,sp.package_code,tp.third_package_code) as  package_number      --集包编码
     ,sp.center_code as unpackage_code2   --二段码包牌对应的拆包网点
     ,tp.center_code as unpackage_code3   --三段码包牌对应的拆包网点
     ,sp.center_name as unpackage_name2   --二段码包牌对应的拆包网点名称
     ,tp.center_name as unpackage_name3   --三段码包牌对应的拆包网点名称
     ,main.apply_reback_type   --退回件类型 4退回,5转寄
     ,main.new_reback_terminal_code   --退转对应三段码
     ,main.reback_apply_time      --退转对应申请时间
     ,main.is_reback_after as is_reback              --是否退转
     ,if(tab_bagging.waybill_no is not null,1,0 ) as is_bagging --中心是否建包
     ,tab_bagging.source as bagging_source --中心建包来源
     ,if(tab_delete.billcode is not null ,1,0) as  is_send_delete --中心发件是否做剔除扫描
     ,null as sign_cande_codes    --签收网点可派货集散code
     ,null as sencond_cande_codes --二段码可派货集散code
     ,null as taking_cande_codes  --揽收网点可派货集散code
     ,null as sign_cande_names    --签收网点可派货集散
     ,null as sencond_cande_names --二段码可派货集散
     ,null as taking_cande_names
     ,case when main.is_reback_before=1 then main.new_receiver_province_id
           when main.is_reback_after=1 then main.old_receiver_province_id
           else tab_waybill.receiver_province_id
    end as  receiver_province_id
     ,case when main.is_reback_before=1 then main.new_receiver_city_id
           when main.is_reback_after=1 then main.old_receiver_city_id
           else tab_waybill.receiver_city_id
    end as  receiver_city_id
     ,case when main.is_reback_before=1 then main.new_receiver_area_id
           when main.is_reback_after=1 then main.old_receiver_area_id
           else tab_waybill.receiver_area_id
    end as  receiver_area_id
     ,coalesce(electr.bag_network_code,sp.create_network_code,tp.create_network_code) as bag_network_code
     ,coalesce(electr.bag_network_name,sp.create_network_name,tp.create_network_name) as bag_network_name
     ,electr.center_code as unpackage_code1   --一段码段码包牌对应的拆包网点
     ,electr.center_name as unpackage_name1   --一段码包牌对应的拆包网点
     ,main.trace_next_code
     ,main.is_reback_before
     ,main.is_happen
     ,main.is_can_cz0
     ,main.trace_next_next_code
     ,date(main.sign_time) as dt
from main_detail main
    left join tab_waybill on main.waybill_no =tab_waybill.waybill_no
    left join tab_taking on tab_taking.waybill_no =main.waybill_no
    left join tab_delete on tab_delete.billcode=main.waybill_no  and tab_delete.scan_time=main.send_time and tab_delete.scan_site_code=main.center_code
    left join tab_bagging on tab_bagging.waybill_no =main.waybill_no and tab_bagging.package_code=main.package_code and  tab_bagging.scan_site_code=main.center_code
    left join jms_dwd.dwd_second_package_list_base_dt sp on sp.dt between date_add('{{ execution_date | cst_ds }}',-60) and '{{ execution_date | cst_ds }}' and  main.package_code=sp.package_number
    left join jms_dwd.dwd_third_package_list_base_dt tp on tp.dt between date_add('{{ execution_date | cst_ds }}',-60) and '{{ execution_date | cst_ds }}' and  main.package_code=tp.package_number
    left join (
    select package_code,package_number,create_network_code as bag_network_code,create_network_name as bag_network_name,center_code,center_name
    from jms_dwd.dwd_tab_electronic_package_list_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-60)  and '{{ execution_date | cst_ds }}'
    ) electr on electr.package_number=main.package_code
    distribute by dt,pmod(hash(waybill_no), 60);
-----------------------------------------------------------------------------------------------------------------------------------------------
--经中间表判断错分类型形成结果表
--经中间表判断错分类型形成结果表
with sys_network_distributi_dt as (  --网点对应的可派货集散点集合处理
    select
        id
         ,network_id
         ,network_code
         ,network_name
         ,create_time as start_time
         ,update_time as end_time
         ,CONCAT_WS(',',collect_list(distribution_points_code)) as out_entrepot_code_new
         ,CONCAT_WS(',',collect_list(distribution_points_name)) as out_entrepot_name_new
    from
        (
            select
                be.id
                 ,be.network_id
                 ,net.code as network_code
                 ,net.name as network_name
                 ,be.create_time
                 ,be.update_time
                 ,be.distribution_points_id
                 ,net2.code as distribution_points_code
                 ,net2.name as distribution_points_name
            from
                (
                    select id,network_id,distribution_points,cast(create_time as timestamp) as create_time ,cast(nvl(update_time,'9999-12-31 23:59:59') as timestamp) as update_time , a.distribution_points_id
                    from jms_dim.dim_sys_network_delivery_log_dt
                             lateral view explode(split(distribution_points,',')) a as distribution_points_id
                    where dt='{{ execution_date | cst_ds }}'
                      and TRIM(distribution_points) !=''
                      and distribution_points is not null  --可派货集散点集合
                    group by id,network_id,distribution_points,create_time ,update_time , distribution_points_id
                )be left join jms_dim.dim_network_whole_massage net on net.id = be.network_id
                    left join jms_dim.dim_network_whole_massage net2 on net2.id = be.distribution_points_id
        )t group by id
                  ,network_id
                  ,network_code
                  ,network_name
                  ,create_time
                  ,update_time
),
     t_distribute_collect_center as ( --集散点对应可派货中心集合
         select
             be.id
              ,be.network_id              --只取集散点
              ,net.code as network_code
              ,net.name as network_name
              ,be.create_time as start_time
              ,be.update_time as end_time
              ,be.distribution_points_id  --可派货中心的
              ,net2.code as distribution_points_code
              ,net2.name as distribution_points_name
              ,net.network_type
         from
             (
                 select id,network_id,delivery_centers,cast(create_time as timestamp) as create_time ,cast(nvl(update_time,'9999-12-31 23:59:59') as timestamp) as update_time , a.distribution_points_id
                 from jms_dim.dim_sys_network_delivery_log_dt
                          lateral view explode(split(delivery_centers,',')) a as distribution_points_id
                 where dt='{{ execution_date | cst_ds }}'
                   and TRIM(delivery_centers) !=''
                   and delivery_centers is not null  --可派货中心集合
                 group by id,network_id,delivery_centers,create_time ,update_time , distribution_points_id
             )be inner join jms_dim.dim_network_whole_massage net on net.id = be.network_id and net.network_type=5
                 left join jms_dim.dim_network_whole_massage net2 on net2.id = be.distribution_points_id
         where  be.create_time<be.update_time
     ),
     t_second as (
         --二段码
         select
             code,network_code,network_name,center_code,center_name,is_enable
              ,case when is_enable=1 then min(update_time)      when is_enable=2 then '1970-12-31 00:00:00'  end as  start_time
              ,case when is_enable=1 then '9999-12-31 00:00:00' when is_enable=2 then max(update_time)       end as  end_time
         from
             (
                 select code,network_code,network_name,center_code,center_name,update_time,is_enable
                 from jms_dim.dim_lmdm_sys_second_code_dt
                 where is_delete=1 and is_enable =1 --and code ='B214-01'
                   and  dt='{{ execution_date | cst_ds }}'
                 group by code,network_code,network_name,center_code,center_name,update_time,is_enable
                 union all
                 select code,network_code,network_name,center_code,center_name,update_time,is_enable
                 from jms_dim.dim_lmdm_sys_second_code_dt
                 where is_delete=1 and is_enable =2 --and code ='B214-01'
                   and dt='{{ execution_date | cst_ds }}'
                 group by code,network_code,network_name,center_code,center_name,update_time,is_enable
             )sec
         group by code,network_code,network_name,center_code,center_name,is_enable
     ),
     t_first as (
         --一段码
         select
             code,center_code,center_name,is_enable
              ,case when is_enable=1 then min(update_time)      when is_enable=2 then '1970-12-31 00:00:00'  end as  start_time
              ,case when is_enable=1 then '9999-12-31 00:00:00' when is_enable=2 then max(update_time)       end as  end_time
         from
             (
                 select code,center_code,center_name,update_time, is_enable
                 from jms_dim.dim_lmdm_sys_first_code
                 where is_delete=1 and is_enable=1
                   --and dt='{{ execution_date | cst_ds }}'
                 group by  code,center_code,center_name,update_time, is_enable
                 union all
                 select code,center_code,center_name,update_time, is_enable
                 from jms_dim.dim_lmdm_sys_first_code
                 where is_delete=1 and is_enable=2
                   --and dt='{{ execution_date | cst_ds }}'
                 group by  code,center_code,center_name,update_time, is_enable
             )fst
         group by code,center_code,center_name,is_enable
     ),
     t_first_dest as (
         --一段码
         select
             code,dest_center_code as center_code ,dest_center_name as center_name,is_enable
              ,case when is_enable=1 then min(update_time)      when is_enable=2 then '1970-12-31 00:00:00'  end as  start_time
              ,case when is_enable=1 then '9999-12-31 00:00:00' when is_enable=2 then max(update_time)       end as  end_time
         from
             (
                 select f.code,f.dest_center_code,f.dest_center_name,f.update_time, f.is_enable
                 from jms_dim.dim_lmdm_sys_first_code f
                          inner join jms_dim.dim_network_whole_massage n on f.center_code= n.code and n.network_type=5
                 where f.is_delete=1 and f.is_enable=1
                   --and dt='{{ execution_date | cst_ds }}'
                 group by  f.code,f.dest_center_code,f.dest_center_name,f.update_time, f.is_enable
                 union all
                 select f.code,f.dest_center_code,f.dest_center_name,f.update_time, f.is_enable
                 from jms_dim.dim_lmdm_sys_first_code f
                          inner join jms_dim.dim_network_whole_massage n on f.center_code= n.code and n.network_type=5
                 where f.is_delete=1 and f.is_enable=1
                  --and dt='{{ execution_date | cst_ds }}'
                 group by  f.code,f.dest_center_code,f.dest_center_name,f.update_time, f.is_enable
             )fst
         group by code,dest_center_code,dest_center_name,is_enable
     ),
--通过二段码code和省市区找对应的目的网点
     t_second_area as (
         --二段码
         select
             code,split(max(network_name),':')[0] as network_code,split(max(network_name),':')[1]  as network_name,province_id,city_id,area_id,is_enable
              ,case when is_enable=1 then min(update_time)      when is_enable=2 then '1970-12-31 00:00:00'  end as  start_time
              ,case when is_enable=1 then '9999-12-31 00:00:00' when is_enable=2 then max(update_time)       end as  end_time
         from
             (
                 select code,concat(network_code,':',network_name) as network_name,province_id,city_id,area_id,update_time,is_enable
                 from jms_dim.dim_lmdm_sys_second_code_dt
                 where is_delete=1 and is_enable =1 --and code ='B214-01'
                   and  dt='{{ execution_date | cst_ds }}'
                 group by code,network_code,network_name,province_id,city_id,area_id ,update_time,is_enable
                 union all
                 select code,concat(network_code,':',network_name) as network_name,province_id,city_id,area_id ,update_time,is_enable
                 from jms_dim.dim_lmdm_sys_second_code_dt
                 where is_delete=1 and is_enable =2 --and code ='B214-01'
                   and dt='{{ execution_date | cst_ds }}'
                 group by code,network_code,network_name,province_id,city_id,area_id,update_time,is_enable
             )sec
         group by code,province_id,city_id,area_id,is_enable
     ),
     t_first_second_collect as (
         select c1.code       as first_code
              , c1.center_code
              , c1.center_name
              , c1.start_time as first_start_time
              , c1.end_time   as first_end_time
              , c1.is_enable  as first_is_enable
              , c2.code       as second_code
              , c2.network_code
              , c2.network_name
              , c2.is_enable   as second_is_enable
              , c2.start_time as second_start_time
              , c2.end_time   as second_end_time
              , c2.distribution_start_time
              , c2.distribution_end_time
         from t_first_dest c1
                  inner join( --二段码维表的center_code为集散类型的时，取集散点的可派货中心作为二段码对应的中心
             select s.code
                  , s.network_code
                  , s.network_name
                  , s.center_code
                  , s.center_name
                  , s.start_time
                  , s.end_time
                  , s.is_enable
                  , dcc.network_code as distribution_network_code
                  , dcc.network_name as distribution_network_name
                  , dcc.distribution_points_code
                  , dcc.distribution_points_name
                  , dcc.start_time   as distribution_start_time
                  , dcc.end_time     as distribution_end_time
             from t_second s
                      inner join jms_dim.dim_network_whole_massage net6
                                 on net6.code = s.center_code and net6.network_type = 5
                      inner join t_distribute_collect_center dcc
                                 on dcc.network_code = s.center_code
         ) c2 on c1.center_code = c2.distribution_points_code
         --where c1.code = '873' --and c2.code='950-39'
     )



insert overwrite table jms_dm.dm_end_center_error_scan_detail_dt partition(dt)
select /*+ BROADCAST(dis1),BROADCAST(dis2),BROADCAST(dis3)*/
    date(t.sign_time) as date_time --查询日期
        ,t.ordersource_name --订单来源
        ,net4.agent_code
        ,net4.agent_name
        ,t.waybill_no       --运单号
        ,t.sign_time          --签收时间
        ,t.sign_site          --签收网点
        ,t.sign_site_code     --签收网点编码
        ,t.send_time       --装车发件时间
        ,net4.name as center_name     --中心名称
        ,t.center_code    --中心编码
        ,t.scan_user      --扫描人
        ,t.scan_user_code --扫描员工号
        ,t.send_source  --中心发件扫描数据来源
        ,t.pistol_id             --扫描设备编码
        ,t.next_station_code     --发件下一站编码
        ,t.next_network_type     --发件下一站类型
        ,t.package_code  --包号
        ,t.first_code
        ,t.second_code
        ,t.third_code
        ,t.dispatch_network_code --运单二段码对应网点code
        ,t.dispatch_network     --运单二段码对应网点
        ,t.taking_site_code   --揽收网点编码
        ,t.taking_site        --揽收网点name
        ,dis1.out_entrepot_code_new as sign_cande_codes    --签收网点可派货集散
        ,dis2.out_entrepot_code_new as sencond_cande_codes --二段码可派货集散
        ,dis3.out_entrepot_code_new as taking_cande_codes  --揽收网点可派货集散
        ,t.package_number      --集包编码
        ,t.reback_type --回件类型 --4退回件 --5转寄
        ,t.is_send_delete as is_delete
        ,--错分类型
    case when next_network_type='4' and (next_station_code=sign_site_code or next_station_code=taking_site_code or next_station_code=t.dispatch_network_code)
    then
    ( case when next_station_code!=trace_next_code and is_can_cz=1 and t.package_code is not null and is_bagging=0 and nvl(package_number,'-')!=nvl(first_code,'')
    then 2
    when next_station_code!=trace_next_code and is_can_cz=1 then 8
    else 0 end)
    when next_network_type='3' and (array_contains(split(dis3.out_entrepot_code_new,',' ),next_station_code) =1
    or array_contains(split(dis2.out_entrepot_code_new,',' ),next_station_code) =1
    or array_contains(split(dis1.out_entrepot_code_new,',' ),next_station_code) =1 )
    --退转，实际下一站为空不判断错装
    then ( case when next_station_code!=trace_next_code and is_can_cz=1 and t.package_code is not null and is_bagging=0 and nvl(package_number,'-')!=nvl(first_code,'')
    then 2
    when next_station_code!=trace_next_code and is_can_cz=1 then 8
    else 0 end)
    when t.second_code is null  or  t.second_code not rlike '^[A-Z0-9]+[0-9]{2}+-[0-9]{2}' then 11 --无二段码
    when t.dispatch_network is null then 12 --二段码无对应网点
    when t.reback_type =4 then 4 --退回件
    when t.reback_type =5 then 5 --转寄件
    when nvl(dispatch_network_code,'')!=sign_site_code  then 1  --二段码异常
    when t.package_code is not null and is_bagging=0 and nvl(package_number,'-')!=nvl(first_code,'') then 2  --上环节建包异常
    when t.package_code is not null and is_bagging=1 and bagging_source='自动分拣设备'
    and ((unpackage_code2 != t.dispatch_network_code and unpackage_code2 is not null) or unpackage_code2 is null)
    and ((unpackage_code3 != t.dispatch_network_code and unpackage_code3 is not null) or unpackage_code3 is null)
    then 3  --设备因素=交叉带  二段码有拆包网点时三段码没有，二者只有一个
    when is_send_delete=1  then 6 --中心发件做删除扫描
    else 7 --人为因素
end as misclass_type --错分类型
    ,--是否错分（重用错分类型判断的代码，主要非错分判断上下要一致）
    case when next_network_type='4' and (next_station_code=sign_site_code or next_station_code=taking_site_code or next_station_code=t.dispatch_network_code)
    then ( case when next_station_code!=trace_next_code and is_can_cz=1 and t.package_code is not null and is_bagging=0 and nvl(package_number,'-')!=nvl(first_code,'')
           then 1
           when next_station_code!=trace_next_code and is_can_cz=1 then 1
           else 0 end)
  when next_network_type='3' and (array_contains(split(dis3.out_entrepot_code_new,',' ),next_station_code) =1
    or array_contains(split(dis2.out_entrepot_code_new,',' ),next_station_code) =1
    or array_contains(split(dis1.out_entrepot_code_new,',' ),next_station_code) =1 )
    --退转，实际下一站为空不判断错装
    then ( case when next_station_code!=trace_next_code and is_can_cz=1 and t.package_code is not null and is_bagging=0 and nvl(package_number,'-')!=nvl(first_code,'')
           then 1
           when next_station_code!=trace_next_code and is_can_cz=1 then 1
           else 0 end)
    else 1
end as is_misclass --是否错分 1错分0非错分
    ,t.next_station
    ,dis1.out_entrepot_name_new as sign_cande_names    --签收网点可派货集散
    ,dis2.out_entrepot_name_new as sencond_cande_names --二段码可派货集散
    ,dis3.out_entrepot_name_new as taking_cande_names  --揽收网点可派货集散
    ,nvl(net4.virt_code,net4.agent_code) as virt_code
    ,nvl(net4.virt_name,net4.agent_name) as virt_name
    ,net4.provider_id
    ,net4.provider_desc as provider_name
    ,coalesce (t.unpackage_code1,t.unpackage_code2,t.unpackage_code3) as unpackage_code --拆包网点
    ,coalesce (t.unpackage_name1,t.unpackage_name2,t.unpackage_name3) as unpackage_name --拆包网点
    ,t.bag_network_code
    ,t.bag_network_name
    --错装的时候显示到件下一站
    , case when next_network_type='4' and (next_station_code=sign_site_code or next_station_code=taking_site_code or next_station_code=t.dispatch_network_code)
    then ( case when next_station_code!=trace_next_code and is_can_cz=1 and t.package_code is not null and is_bagging=0 and nvl(package_number,'-')!=nvl(first_code,'')
                then null
                when next_station_code!=trace_next_code and is_can_cz=1 then t.trace_next_code
                else null end)
    when next_network_type='3' and (array_contains(split(dis3.out_entrepot_code_new,',' ),next_station_code) =1
    or array_contains(split(dis2.out_entrepot_code_new,',' ),next_station_code) =1
    or array_contains(split(dis1.out_entrepot_code_new,',' ),next_station_code) =1 )
    --退转，实际下一站为空不判断错装
    then ( case when next_station_code!=trace_next_code and is_can_cz=1 and t.package_code is not null and is_bagging=0 and nvl(package_number,'-')!=nvl(first_code,'')
           then null
           when next_station_code!=trace_next_code and is_can_cz=1 then t.trace_next_code
           else null end)
     else null
end as arr_next_code  --实际轨迹到件下一站（只取集散或网点）
    --,net5.name as arr_next_name
    , case when next_network_type='4' and (next_station_code=sign_site_code or next_station_code=taking_site_code or next_station_code=t.dispatch_network_code)
    then ( case when next_station_code!=trace_next_code and is_can_cz=1 and t.package_code is not null and is_bagging=0 and nvl(package_number,'-')!=nvl(first_code,'')
                then null
                when next_station_code!=trace_next_code and is_can_cz=1 then net5.name
                else null end)
    when next_network_type='3' and (array_contains(split(dis3.out_entrepot_code_new,',' ),next_station_code) =1
    or array_contains(split(dis2.out_entrepot_code_new,',' ),next_station_code) =1
    or array_contains(split(dis1.out_entrepot_code_new,',' ),next_station_code) =1 )
    --退转，实际下一站为空不判断错装
    then ( case when next_station_code!=trace_next_code and is_can_cz=1 and t.package_code is not null and is_bagging=0 and nvl(package_number,'-')!=nvl(first_code,'')
           then null
           when next_station_code!=trace_next_code and is_can_cz=1 then net5.name
           else null end)
     else null
end as arr_next_name  --实际轨迹到件下一站（只取集散或网点）
    ,t.dt
from
(   select
    *
    from
    (
    select /*+ BROADCAST(fst),BROADCAST(scd),BROADCAST(c2),BROADCAST(sec2)*/
        way.waybill_no
        ,way.sign_time
        ,way.sign_site
        ,way.sign_site_code
        ,way.send_time
        ,way.center_name
        ,way.center_code
        ,way.scan_user
        ,way.scan_user_code
        ,way.bagging_source as send_source --取建包扫描数据来源
        ,way.pistol_id
        ,way.next_station_code
        ,way.next_network_type
        ,way.next_station
        ,way.package_code
        ,way.ordersource_name
        ,COALESCE(scd.network_code,c2.network_code,sec2.network_code) as dispatch_network_code
        ,COALESCE(scd.network_name,c2.network_name,sec2.network_name) as dispatch_network
        ,way.taking_site_code
        ,way.taking_site
        ,way.package_number
        ,way.unpackage_code2
        ,way.unpackage_code3
        ,way.unpackage_name2
        ,way.unpackage_name3
        ,way.reback_type
        ,way.reback_terminal_code
        ,way.reback_apply_time
        ,way.is_reback
        ,way.is_bagging
        ,way.bagging_source
        ,way.is_send_delete
        ,way.first_code         --一段码
        ,way.second_code        --二段码
        ,way.third_code         --三段码
        ,way.receiver_province_id
        ,way.receiver_city_id
        ,way.receiver_area_id
        ,way.bag_network_code
        ,way.bag_network_name
        ,way.dt
        ,way.unpackage_code1
        ,way.unpackage_name1
        ,way.trace_next_code
        ,way.is_reback_before
        ,way.is_happen
        ,if(way.is_happen=1  --实际轨迹到件站点是否在之前出现过
            or way.is_reback_before=1  --有退转
            or way.is_reback=1         --有退转
            or way.is_can_cz0=0        --实际发件扫描到件时间和下下一站不为空的时候可判断错分
            or way.trace_next_code=way.taking_site_code --实际轨迹到件站点=揽收网点
            or way.trace_next_next_code!=way.center_code
            --实际轨迹到件站点不等于签收网点 以上条件满足一条不判错装
            or nvl(way.next_station_code,'')!=nvl(way.sign_site_code,'') ,0,1) as is_can_cz --是否判断错装
        ,row_number() over(partition by way.waybill_no,way.sign_time,way.center_code,way.next_station_code order by fst.is_enable,scd.is_enable,c2.first_is_enable,c2.second_is_enable,sec2.is_enable) as rnk
    from jms_tmp.tmp_end_center_error_scan_detail_mid_dt way
    --一二段码取到对应目的网点
    left join t_first fst on  fst.code=way.first_code and way.send_time between fst.start_time and fst.end_time
    left join t_second scd on  fst.center_code =scd.center_code  and scd.code=way.second_code  and  way.send_time between scd.start_time and scd.end_time
    --根据二段码的目的网点的可派货中心取目地中心
    left join t_first_second_collect c2
    on way.first_code=c2.first_code  and way.second_code=c2.second_code
    and  way.send_time between c2.first_start_time and c2.first_end_time
    and  way.send_time between c2.second_start_time and c2.second_end_time
    and  way.send_time between c2.distribution_start_time and c2.distribution_end_time
    --根据二段码省市区随机取一个目的网点
    left join t_second_area sec2 on  sec2.province_id= way.receiver_province_id  and sec2.city_id= way.receiver_city_id  and sec2.area_id= way.receiver_area_id  and sec2.code= way.second_code
    and way.send_time between sec2.start_time and sec2.end_time
    where way.dt='{{ execution_date | cst_ds }}'
    )tmp where rnk=1
) t
left join jms_dim.dim_network_whole_massage net4 on net4.code=t.center_code
left join jms_dim.dim_network_whole_massage net1 on net1.code=t.sign_site_code
left join jms_dim.dim_network_whole_massage net2 on net2.code=t.dispatch_network_code
left join jms_dim.dim_network_whole_massage net3 on net3.code=t.taking_site_code
left join jms_dim.dim_network_whole_massage net5 on net5.code=t.trace_next_code
left join sys_network_distributi_dt dis1 on dis1.network_code=t.sign_site_code and t.send_time between dis1.start_time and dis1.end_time
left join sys_network_distributi_dt dis2 on dis2.network_code=t.dispatch_network_code and t.send_time between dis2.start_time and dis2.end_time
left join sys_network_distributi_dt dis3 on dis3.network_code=t.taking_site_code and t.send_time between dis3.start_time and dis3.end_time
distribute by dt, abs(hash(t.waybill_no)) % 50;
